﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;
namespace Human
{
	/// <summary>
	/// Summary description for frmBCDanhSachVaTienLuongCBCC.
	/// </summary>
	public class frmBCdslgCBCC : System.Windows.Forms.Form
	{
        private AccessData mDB = new AccessData();
        Language lan = new Language();
		private DataSet ds =new DataSet();
        string tungay = "",s_mmyy="",s_bophan="",user_dn="";
		private frmReport rpt;
        private ExportToExcel p = new ExportToExcel();
		private System.Windows.Forms.Button butKetThuc;
		private System.Windows.Forms.Label label3;
		private System.Windows.Forms.Button butBaoCao;
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.RadioButton rdbMayIn;
		private System.Windows.Forms.RadioButton rdbManHinh;
		private MaskedBox.MaskedBox txbTuNgay;
		private System.Windows.Forms.RadioButton radexcel;		
    	private System.ComponentModel.Container components = null;

		public frmBCdslgCBCC(string s_makp,string s_userdn)
		{
			InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
		}

    	protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(frmBCdslgCBCC));
            this.butKetThuc = new System.Windows.Forms.Button();
            this.label3 = new System.Windows.Forms.Label();
            this.butBaoCao = new System.Windows.Forms.Button();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.radexcel = new System.Windows.Forms.RadioButton();
            this.rdbMayIn = new System.Windows.Forms.RadioButton();
            this.rdbManHinh = new System.Windows.Forms.RadioButton();
            this.txbTuNgay = new MaskedBox.MaskedBox();
            this.groupBox1.SuspendLayout();
            this.SuspendLayout();
            // 
            // butKetThuc
            // 
            this.butKetThuc.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butKetThuc.Image = ((System.Drawing.Image)(resources.GetObject("butKetThuc.Image")));
            this.butKetThuc.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butKetThuc.Location = new System.Drawing.Point(146, 116);
            this.butKetThuc.Name = "butKetThuc";
            this.butKetThuc.Size = new System.Drawing.Size(75, 28);
            this.butKetThuc.TabIndex = 3;
            this.butKetThuc.Text = "Kết thúc";
            this.butKetThuc.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.butKetThuc.Click += new System.EventHandler(this.butKetThuc_Click);
            // 
            // label3
            // 
            this.label3.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label3.Location = new System.Drawing.Point(50, 21);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(57, 16);
            this.label3.TabIndex = 13;
            this.label3.Text = "Có đến :";
            this.label3.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // butBaoCao
            // 
            this.butBaoCao.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butBaoCao.Image = ((System.Drawing.Image)(resources.GetObject("butBaoCao.Image")));
            this.butBaoCao.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butBaoCao.Location = new System.Drawing.Point(65, 116);
            this.butBaoCao.Name = "butBaoCao";
            this.butBaoCao.Size = new System.Drawing.Size(75, 28);
            this.butBaoCao.TabIndex = 2;
            this.butBaoCao.Text = "Báo cáo";
            this.butBaoCao.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.butBaoCao.Click += new System.EventHandler(this.butBaoCao_Click);
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.radexcel);
            this.groupBox1.Controls.Add(this.rdbMayIn);
            this.groupBox1.Controls.Add(this.rdbManHinh);
            this.groupBox1.Location = new System.Drawing.Point(14, 48);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(240, 53);
            this.groupBox1.TabIndex = 1;
            this.groupBox1.TabStop = false;
            // 
            // radexcel
            // 
            this.radexcel.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.radexcel.Location = new System.Drawing.Point(164, 16);
            this.radexcel.Name = "radexcel";
            this.radexcel.Size = new System.Drawing.Size(59, 20);
            this.radexcel.TabIndex = 3;
            this.radexcel.Text = "Excel";
            this.radexcel.KeyDown += new System.Windows.Forms.KeyEventHandler(this.radexcel_KeyDown);
            // 
            // rdbMayIn
            // 
            this.rdbMayIn.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.rdbMayIn.Location = new System.Drawing.Point(93, 16);
            this.rdbMayIn.Name = "rdbMayIn";
            this.rdbMayIn.Size = new System.Drawing.Size(65, 20);
            this.rdbMayIn.TabIndex = 2;
            this.rdbMayIn.Text = "Máy in";
            this.rdbMayIn.KeyDown += new System.Windows.Forms.KeyEventHandler(this.rdbMayIn_KeyDown);
            // 
            // rdbManHinh
            // 
            this.rdbManHinh.Checked = true;
            this.rdbManHinh.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.rdbManHinh.Location = new System.Drawing.Point(8, 16);
            this.rdbManHinh.Name = "rdbManHinh";
            this.rdbManHinh.Size = new System.Drawing.Size(79, 20);
            this.rdbManHinh.TabIndex = 1;
            this.rdbManHinh.TabStop = true;
            this.rdbManHinh.Text = "Màn hình";
            this.rdbManHinh.KeyDown += new System.Windows.Forms.KeyEventHandler(this.rdbManHinh_KeyDown);
            // 
            // txbTuNgay
            // 
            this.txbTuNgay.ForeColor = System.Drawing.Color.Blue;
            this.txbTuNgay.Location = new System.Drawing.Point(112, 20);
            this.txbTuNgay.Mask = "##/##/####";
            this.txbTuNgay.MaxLength = 8;
            this.txbTuNgay.Name = "txbTuNgay";
            this.txbTuNgay.Size = new System.Drawing.Size(88, 20);
            this.txbTuNgay.TabIndex = 0;
            this.txbTuNgay.Text = "__/__/____";
            this.txbTuNgay.Validated += new System.EventHandler(this.txbTuNgay_Validated);
            this.txbTuNgay.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txbTuNgay_KeyDown);
            // 
            // frmBCdslgCBCC
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(272, 152);
            this.Controls.Add(this.txbTuNgay);
            this.Controls.Add(this.label3);
            this.Controls.Add(this.butKetThuc);
            this.Controls.Add(this.butBaoCao);
            this.Controls.Add(this.groupBox1);
            this.ForeColor = System.Drawing.Color.DarkBlue;
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.Name = "frmBCdslgCBCC";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Danh sách và Tiền lương CBCCVC";
            this.Load += new System.EventHandler(this.frmBCDanhSachVaTienLuongCBCC_Load);
            this.groupBox1.ResumeLayout(false);
            this.ResumeLayout(false);
            this.PerformLayout();

		}
		#endregion

		private void frmBCDanhSachVaTienLuongCBCC_Load(object sender, System.EventArgs e)
		{
			DateTime dt=DateTime.Now,dt1;
			dt1=dt.AddYears(1);
			txbTuNgay.Text=dt.Day.ToString().PadLeft(2,'0')+"/"+dt.Month.ToString().PadLeft(2,'0')+"/"+dt.Year.ToString();
		}

		private void butKetThuc_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}
		private void print(int prn)
		{
            tungay = txbTuNgay.Text.ToString();
            s_mmyy = tungay.Substring(3, 2).Trim() + tungay.Substring(8, 2).Trim();
			try
			{
                DataSet dstmp = new DataSet();
                ExportToExcel exp = new ExportToExcel();
				string sql="SELECT '"+txbTuNgay.Text+"',case when hd.loaihd='BC' then 'A - CÔNG CHỨC GIỮ";
                sql += " CHỨC VỤ LÃNH ĐẠO VÀ CÔNG CHỨC CHUYÊN MÔN' else 'B - HỢP ĐỒNG";
				sql += " TRONG CHỈ TIÊU BIÊN CHẾ'end AS A";
				sql += " ,hd.manv, l.ho||' '||l.ten AS hoten";
				sql += " ,case when l.phai='Nam' then namsinh else '' end AS Nam";
                sql += " ,case when l.phai='Nữ' then namsinh else '' end AS Nu";
				sql += " ,case when mc.tenvt<>'' then mc.tenvt else mc.diengiai end AS chucvu";			
                sql += " ,l.chinhtri AS ct, hd.mangach AS msn";
				sql += " ,to_char(hd.ngaybnngach,'dd/mm/yyyy') AS tgx";
				sql += " ,hs_nv.h_hslc AS hslg,hs_nv.h_hscv AS cvtc ,hs_nv.h_hskv AS kv,hs_nv.h_hsth AS thuhut";
				sql += " ,hs_nv.h_hstn AS trnh ,hs_nv.h_hsdh AS dh,hs_nv.h_hsld AS ld,hs_nv.h_hsdb AS pck";
				sql += " ,hs.mltt,hs_nv.h_hsud ,hs.mltt*hs_nv.h_hslc AS lcb, hs.mltt*(hs_nv.h_hslc+hs_nv.h_hscv+hs_nv.h_hslc*hs_nv.h_hsvk)as tlcb";
				sql += " ,hd.luonghd * (hd.hs_kv + hd.hs_tn + hd.hs_dh + hd.hs_ld + hd.hs_db + (hd.hs_lc+hd.hs_cv+hd.hs_lc*hd.hs_vk)*hd.hs_ud/100) AS tpc";
                sql += " ,hs.mltt * (hs_nv.h_hskv + hs_nv.h_hstn + hs_nv.h_hsdh + hs_nv.h_hsld + hs_nv.h_hsdb + (hs_nv.h_hslc+hs_nv.h_hscv+hs_nv.h_hslc*hs_nv.h_hsvk)*hs_nv.h_hsud/100) AS tpc";
                sql += " ,hs.mltt*(hs_nv.h_hslc+hs_nv.h_hscv+hs_nv.h_hslc*hs_nv.h_hsvk)+ hs.mltt * ";
                sql += " (hs_nv.h_hskv + hs_nv.h_hstn + hs_nv.h_hsdh + hs_nv.h_hsld + hs_nv.h_hsdb+ (hs_nv.h_hslc+hs_nv.h_hscv+hs_nv.h_hslc*hs_nv.h_hsvk)*hs_nv.h_hsud/100) AS tongcong"; //tlcb+tpc
                sql += " FROM " + mDB.user + ".llnv AS l inner join " + mDB.user + ".hopdong AS hd on l.manv=hd.manv ";
                sql += " left join  " + mDB.user + ".dmcv AS mc on hd.macv=mc.macv";
                sql += " left join " + mDB.user + ".mahd AS mh on hd.loaihd=mh.mahd";
                sql += " left join " + mDB.user + ".hocvan AS hv on l.mahv=hv.mahv";
                sql += " left join " + mDB.user + ".ctdaotao AS ct on l.nnghiep=ct.mactdt";
                sql += " left join " + mDB.user + ".ngoaingu AS nn on l.mann=nn.mann";
                sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv";
                sql += " inner join " + mDB.user + ".heso_nv_"+ s_mmyy +" hs_nv on hs_nv.manv=l.manv ";// cac he so khac
                sql += " inner join " + mDB.user + ".heso hs on hs.mmyy=hs_nv.mmyy"; //table heso chi lay mltt=luonghd 

                sql += " WHERE to_timestamp(hd.ngayhd,'dd/mm/yyyy') <=to_timestamp('" + txbTuNgay.Text.Trim() + "','dd/mm/yyyy')";
                sql += " and (hd.ngaynghi is null or hd.ngaynghi>=to_timestamp('" + txbTuNgay.Text.Trim() + "','dd/mm/yyyy'))";
                sql += " and hd.lan=ml.lan";
                sql+=" order by hd.hs_cv desc";
				ds=mDB.get_data(sql);
                ds.WriteXml("..\\xml\\h_dscongchucvatienluong.xml", XmlWriteMode.WriteSchema);
				if (ds.Tables[0].Rows.Count>0)
				{
                    switch (prn)
                    {
                        case 1: frmReport f = new frmReport(mDB, ds,lan.Change_language_MessageText( "BÁO CÁO DANH SÁCH VÀ TIỀN LƯƠNG CÁN BỘ , CÔNG CHỨC"), "h_dscongchucvatienluong.rpt", true, 0,user_dn);
                            f.ShowDialog(this);
                            break;
                        case 2:
                        case 3: dstmp = exp_exel(ds);
                            exp.exp_excel_bclcbcc(dstmp, "bao cao ds luong cbcc", txbTuNgay.Text);
                            break;
                    }                   
                   
				}
				else
					MessageBox.Show(lan.Change_language_MessageText("Chưa có đầy đủ dữ liệu"),lan.Change_language_MessageText("Thông Báo"));
			}
			catch{}
		}
        private DataSet exp_exel(DataSet dse)
        {
            string[] str = new string[] {lan.Change_language_MessageText( "STT"),lan.Change_language_MessageText( "Họ Và Tên"),lan.Change_language_MessageText( "Nam"),lan.Change_language_MessageText( "Nữ"),lan.Change_language_MessageText( "Chức Vụ"),lan.Change_language_MessageText( "Mã Ngạch"),lan.Change_language_MessageText( "HS Lương"),lan.Change_language_MessageText( "TG xếp"),lan.Change_language_MessageText( "CV Tái Cử"),lan.Change_language_MessageText( "Khu vực"),lan.Change_language_MessageText( "Thu Hút"),lan.Change_language_MessageText( "Trách Nhiệm"),lan.Change_language_MessageText( "Độc Hại"),lan.Change_language_MessageText("Lưu Động"),lan.Change_language_MessageText( "Lương CB"),lan.Change_language_MessageText( "Tiền PC"),lan.Change_language_MessageText( "Tổng Cộng") };
            DataSet dse1 = new DataSet();
            dse1.Tables.Add();
            dse1.Tables[0].Columns.Add("stt", typeof(string));
            dse1.Tables[0].Columns.Add("A", typeof(string));
            dse1.Tables[0].Columns.Add("hoten", typeof(string));
            dse1.Tables[0].Columns.Add("Nam", typeof(string));
            dse1.Tables[0].Columns.Add("Nu", typeof(string));
            dse1.Tables[0].Columns.Add("chucvu", typeof(string));
            dse1.Tables[0].Columns.Add("msn", typeof(string));
            dse1.Tables[0].Columns.Add("hslg", typeof(string));
            dse1.Tables[0].Columns.Add("tgx", typeof(string));
            dse1.Tables[0].Columns.Add("cvtc", typeof(string));
            dse1.Tables[0].Columns.Add("kv", typeof(string));
            dse1.Tables[0].Columns.Add("thuhut", typeof(string));
            dse1.Tables[0].Columns.Add("trnh", typeof(string));
            dse1.Tables[0].Columns.Add("dh", typeof(string));
            dse1.Tables[0].Columns.Add("ld", typeof(string));
            dse1.Tables[0].Columns.Add("tlcb", typeof(string));
            dse1.Tables[0].Columns.Add("tpc", typeof(string));
            dse1.Tables[0].Columns.Add("tongcong", typeof(string));
            //dse1.Tables[0].Columns.Add("cvtc", typeof(string));
            int stt = 0;
            DataRow nrow;
            foreach (DataRow r in dse.Tables[0].Rows)
            {
                stt++;
                nrow = dse1.Tables[0].NewRow();
                nrow["stt"] = stt;
                nrow["A"] = r["A"].ToString();
                nrow["hoten"] = r["hoten"].ToString();
                nrow["Nam"] = r["Nam"].ToString();
                nrow["Nu"] = r["Nu"].ToString();
                nrow["chucvu"] = r["chucvu"].ToString();
                nrow["msn"] = r["msn"].ToString();
                nrow["hslg"] = r["hslg"].ToString();
                nrow["tgx"] = r["tgx"].ToString();
                nrow["cvtc"] = r["cvtc"].ToString();
                nrow["kv"] = r["kv"].ToString();
                nrow["thuhut"] = r["thuhut"].ToString();
                nrow["trnh"] = r["trnh"].ToString();
                nrow["dh"] = r["dh"].ToString();
                nrow["ld"] = r["ld"].ToString();
                nrow["tlcb"] = r["tlcb"].ToString();
                nrow["tpc"] = r["tpc"].ToString();
                nrow["tongcong"] = r["tongcong"].ToString();
                 
                dse1.Tables[0].Rows.Add(nrow);
            }
            return dse1;
        }
		private string stringtoMMDDYYYY(string s)
		{
			return s.Substring(3,2)+"/"+s.Substring(0,2)+"/"+s.Substring(6,4);
		}

		private int kiemtrangay(int dtemp,int y)
		{
			int d=0;
			
			switch(dtemp)
			{
				case 1:
				case 3:
				case 5:
				case 7:
				case 8:
				case 10:
				case 12:
					d=31;
					break;
				case 2:
					if((y%4)==0)
						d=29;
					else if((y%4)!=0)
						d=28;
					break;
				case 4:
				case 6:
				case 9:
				case 11:
					d=30;
					break;
			}
			return d;
		}
		private void txbTuNgay_Validated(object sender, System.EventArgs e)
		{
			if(!mDB.kiemtrangayhople(txbTuNgay.Text.Trim()))
                txbTuNgay.Focus();
		}

		private void butBaoCao_Click(object sender, System.EventArgs e)
		{
			if(rdbManHinh.Checked==true) print(1);
			if(rdbMayIn.Checked==true) print(2);
			if(radexcel.Checked==true) print(3);
			butKetThuc.Focus();
		}

		private void txbTuNgay_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				txbTuNgay.Focus();
		}

		private void rdbManHinh_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				butBaoCao.Focus();
		}

		private void rdbMayIn_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				butBaoCao.Focus();
		}

		private void radexcel_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				butBaoCao.Focus();
		}
	}
}
